package com.rickpan.repository;

import com.rickpan.entity.TeamKey;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 团队密钥数据访问层
 * 
 * @author RickPan Team
 * @since 2025-07-19
 */
@Repository
public interface TeamKeyRepository extends JpaRepository<TeamKey, Long> {

    /**
     * 查找团队的当前活跃密钥
     * 
     * @param teamId 团队ID
     * @return 当前活跃密钥
     */
    @Query("SELECT tk FROM TeamKey tk WHERE tk.teamId = :teamId AND tk.status = 'active' ORDER BY tk.keyVersion DESC")
    List<TeamKey> findActiveKeysByTeamIdOrderByVersionDesc(@Param("teamId") Long teamId);

    default Optional<TeamKey> findCurrentActiveKey(Long teamId) {
        List<TeamKey> keys = findActiveKeysByTeamIdOrderByVersionDesc(teamId);
        return keys.isEmpty() ? Optional.empty() : Optional.of(keys.get(0));
    }

    /**
     * 查找团队指定版本的密钥
     * 
     * @param teamId 团队ID
     * @param keyVersion 密钥版本
     * @return 指定版本的密钥
     */
    Optional<TeamKey> findByTeamIdAndKeyVersion(Long teamId, Integer keyVersion);

    /**
     * 查找团队的所有密钥（按版本倒序）
     * 
     * @param teamId 团队ID
     * @return 团队所有密钥列表
     */
    @Query("SELECT tk FROM TeamKey tk WHERE tk.teamId = :teamId ORDER BY tk.keyVersion DESC")
    List<TeamKey> findAllByTeamIdOrderByVersionDesc(@Param("teamId") Long teamId);



    /**
     * 查找即将过期的密钥
     * 
     * @param beforeTime 过期时间点
     * @return 即将过期的密钥列表
     */
    @Query("SELECT tk FROM TeamKey tk WHERE tk.status = 'active' AND tk.expiresAt IS NOT NULL AND tk.expiresAt <= :beforeTime")
    List<TeamKey> findExpiringKeys(@Param("beforeTime") LocalDateTime beforeTime);

    /**
     * 查找已过期但状态仍为活跃的密钥
     * 
     * @param currentTime 当前时间
     * @return 已过期的密钥列表
     */
    @Query("SELECT tk FROM TeamKey tk WHERE tk.status = 'active' AND tk.expiresAt IS NOT NULL AND tk.expiresAt < :currentTime")
    List<TeamKey> findExpiredActiveKeys(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 获取团队的最大密钥版本号
     * 
     * @param teamId 团队ID
     * @return 最大版本号
     */
    @Query("SELECT COALESCE(MAX(tk.keyVersion), 0) FROM TeamKey tk WHERE tk.teamId = :teamId")
    Integer findMaxKeyVersion(@Param("teamId") Long teamId);

    /**
     * 统计团队的密钥数量
     * 
     * @param teamId 团队ID
     * @return 密钥数量
     */
    long countByTeamId(Long teamId);

    /**
     * 统计团队的活跃密钥数量
     * 
     * @param teamId 团队ID
     * @return 活跃密钥数量
     */
    @Query("SELECT COUNT(tk) FROM TeamKey tk WHERE tk.teamId = :teamId AND tk.status = 'active'")
    long countActiveKeysByTeamId(@Param("teamId") Long teamId);

    /**
     * 批量更新密钥状态
     * 
     * @param keyIds 密钥ID列表
     * @param status 新状态
     * @return 更新数量
     */
    @Query("UPDATE TeamKey tk SET tk.status = :status WHERE tk.id IN :keyIds")
    int updateKeyStatus(@Param("keyIds") List<Long> keyIds, @Param("status") TeamKey.KeyStatus status);

    /**
     * 撤销团队的所有旧版本密钥（保留指定版本）
     * 
     * @param teamId 团队ID
     * @param keepVersion 保留的版本号
     * @return 撤销数量
     */
    @Query("UPDATE TeamKey tk SET tk.status = 'revoked' WHERE tk.teamId = :teamId AND tk.keyVersion < :keepVersion AND tk.status = 'active'")
    int revokeOldKeys(@Param("teamId") Long teamId, @Param("keepVersion") Integer keepVersion);

    /**
     * 删除团队的所有密钥（团队删除时使用）
     * 
     * @param teamId 团队ID
     */
    void deleteByTeamId(Long teamId);

    /**
     * 查找用户创建的密钥
     * 
     * @param createdBy 创建者ID
     * @return 用户创建的密钥列表
     */
    @Query("SELECT tk FROM TeamKey tk WHERE tk.createdBy = :createdBy ORDER BY tk.createdAt DESC")
    List<TeamKey> findByCreatedByOrderByCreatedAtDesc(@Param("createdBy") Long createdBy);

    /**
     * 查找指定算法的密钥
     * 
     * @param algorithm 加密算法
     * @return 使用指定算法的密钥列表
     */
    List<TeamKey> findByAlgorithm(String algorithm);

    /**
     * 统计各状态密钥的数量
     * 
     * @param teamId 团队ID
     * @return 统计结果 [status, count]
     */
    @Query("SELECT tk.status, COUNT(tk) FROM TeamKey tk WHERE tk.teamId = :teamId GROUP BY tk.status")
    List<Object[]> countKeysByStatus(@Param("teamId") Long teamId);
}
